Querying EPT data

These small samples shows how you can write code work with EPT document from a Content Studio Web page.

A simple XML filter query

The following code sample shows how to use the FilterScalar method to the value of an inventory item in a fictive inventory system. In this system the InventoryNumber is always unique thus we can be sure that only one or none item exists that satisfies the criteria. In this case the FilterScalar method is optimal to use.

The FilterScalar method is optimized for performance when you know that only one record can be found, for example when you filter on the identifier of the document.

Note
Please observe the usage of single quotation marks around the filter argument ( [InventoryNo] = '234875'). Even though that the data to compare is an integer, it is stored in the database as a string. In Sql Server it is possible to do an exact integer matching but this approach is not optimal for performance since Sql Server must implicitly convert the values to an integer before the comparison is done. In this case it is better to do a direct string comparison instead, something that will return the same result.

//Define the category id to select from.
int categoryId = 200;
//Create a new instance of the XmlFilter object.
try
{
  XmlFilter xfi = new XmlFilter(CS_ConnectionId,
                                CS_UserSessionId, 
                                categoryID);
  //Add the field to return first (document id is always returned)
  xfi.AddField("ItemName", true);
  //Add a field to filter on
  xfi.AddField("InventoryNo", false);
  //Add a filter condition
  xfi.FilterCriteria = "[InventoryNo] = '234875'";
  //Get the name of this item and its document id
  string ItemName;
  int documentID = xfi.FilterScalar("ItemName", out ItemName);
  //Anything found?
  if(documentID != 0)
    Response.Write("Item " + ItemName + " found");
  else
    Response.Write("No item could be found");
}
catch (Exception ex)
{
  Response.Write("Error querying data. " + ex.Message);
}

VB.NET

'Define the category id to select from.
Dim categoryId As Integer = 200;
'Create a new instance of the XmlFilter object.
Try
  Dim xfi As New XmlFilter(CS_ConnectionId, _
                           CS_UserSessionId, _
                           categoryID)
  'Add the fields to return first (document id is always returned)
  xfi.AddField("ItemName", True)
  'Add a field to filter on
  xfi.AddField("InventoryNo", False)
  'Add a filter condition
  xfi.FilterCriteria = "[InventoryNo] = '234875'"
  'Get the name of this item and its document id
  Dim ItemName As String
  Dim documentID As Integer = xfi.FilterScalar("ItemName", ItemName)
  'Anything found?
  If documentID <> 0
    Response.Write("Item " & ItemName & " found")
  Else
    Response.Write("No item could be found")
  End If
Catch ex As Exception
  Response.Write("Error querying data. " & ex.Message)
End Try

An XML filter query that returns multiple data fields

The following sample code shows how you can use the XmlFilter class to perform an Xml filter query in Content Studio. In this code two fields return data (Head and Intro) and two additional fields (Area and CS_PublishDate) is used for filtering and sorting only. The data is returned as an XmlFilterReader object that you can use to navigate through the returned data.

int categoryID = 168;
//Create a new instance of the XmlFilter object
ContentStudio.Document.EPT.XmlFilter xfi = 
   new ContentStudio.Document.EPT.XmlFilter(CS_ConnectionId, 
                                            CS_UserSessionId, 
                                            categoryID);
//Add the two fields that return data first.
xfi.AddField("Head", true);
xfi.AddField("Intro", true);
//Add fields to filter and sort on that does not return data
xfi.AddField("Area");
xfi.AddField("CS_PublishDate", ContentStudio.Document.EPT.XmlIndexQuery.QueryDataTypes.DateTime);
//Define a criteria and a sort command
xfi.FilterCriteria = "[Area] > 1173";
xfi.SortCommand = "[CS_PublishDate] DESC";
//Limit the number of records
xfi.PageSize = 10;
using (XmlFilterReader xre = xfi.FilterReader())
{
    //Get the number of records and pages
    Response.Write("RecordCount: " + xfi.RecordCount.ToString() + "<br />");
    Response.Write("PageCount: " + xfi.PageCount.ToString() + "<br />");
    Response.Write("<table>" + Environment.NewLine);
    //Get the data
    while (xre.Read())
    {
        Response.Write("<tr>" + Environment.NewLine);
        Response.Write("<td>" + Server.HtmlEncode(xre["Head"]) + "</td>");
        Response.Write("<td>" + Server.HtmlEncode(xre["Intro"]) + "</td>");
        Response.Write("</tr>" + Environment.NewLine);
    }
    Response.Write("</table>" + Environment.NewLine);
}

VB.NET

Dim categoryID As Integer = 168
'Create a new instance of the XmlFilter object
Dim xfi As New ContentStudio.Document.EPT.XmlFilter(CS_ConnectionId, _
                                                    CS_UserSessionId, _
                                                    categoryID)
'Add the two fields that return data
xfi.AddField("Head", true)
xfi.AddField("Intro", true)
'Add fields to filter and sort on that does not return data
xfi.AddField("Area")
xfi.AddField("CS_PublishDate", ContentStudio.Document.EPT.XmlIndexQuery.QueryDataTypes.DateTime)
'Define a criteria and a sort command
xfi.FilterCriteria = "[Area] > 1173"
xfi.SortCommand = "[CS_PublishDate] DESC"
'Limit the number of records
xfi.PageSize = 10
Response.Write("<table>" & Environment.NewLine)
Using xre As XmlFilterReader = xfi.FilterReader()
    'Get the data
    While xre.Read()
        Response.Write("<tr>" & Environment.NewLine)
        Response.Write("<td>" & Server.HtmlEncode(xre("Head")) & "</td>")
        Response.Write("<td>" & Server.HtmlEncode(xre("Intro")) & "</td>")
        Response.Write("</tr>" & Environment.NewLine)
    End While
    Response.Write("</table>" & Environment.NewLine)
End Using

An XML filter query that reads data in pages

The following sample code shows how you can use the XmlFilter class to perform an Xml filter query in Content Studio. A XmlFilterReader object is used to navigate through the returned data on data page at the time.

int categoryID = 168;
//Create a new instance of the XmlFilter object
ContentStudio.Document.EPT.XmlFilter xfi = 
   new ContentStudio.Document.EPT.XmlFilter(CS_ConnectionId, 
                                            CS_UserSessionId, 
                                            categoryID);
//Add the two fields that return data first.
xfi.AddField("Head", true);
xfi.AddField("Intro", true);
//Add fields to filter and sort on that does not return data
xfi.AddField("Area");
xfi.AddField("CS_PublishDate", ContentStudio.Document.EPT.XmlIndexQuery.QueryDataTypes.DateTime);
//Define a criteria and a sort command
xfi.FilterCriteria = "[Area] > 1173";
xfi.SortCommand = "[CS_PublishDate] DESC";
//Limit the number of records
xfi.PageSize = 10;
Response.Write("<table>" + Environment.NewLine);
do
{
   //Get the data page by page
   using (XmlFilterReader xre = xfi.FilterReader())
   {
      while (xre.Read())
      {
          Response.Write("<tr>");
          Response.Write("<td>" + Server.HtmlEncode(xre["Head"]) + "</td>");
          Response.Write("<td>" + Server.HtmlEncode(xre["Intro"]) + "</td>");
          Response.Write("</tr>\r\n");
      }
      //get the next data page
      xfi.PageNumber++;
   }
}(while xfi.PageNumber <= xfi.PageCount);
Response.Write("</table>" + Environment.NewLine);

VB.NET

Dim categoryID As Integer = 168
'Create a new instance of the XmlFilter object
Dim xfi As New ContentStudio.Document.EPT.XmlFilter(CS_ConnectionId, _
                                                    CS_UserSessionId, _
                                                    categoryID)
'Add the two fields that return data
xfi.AddField("Head", true)
xfi.AddField("Intro", true)
'Add fields to filter and sort on that does not return data
xfi.AddField("Area")
xfi.AddField("CS_PublishDate", ContentStudio.Document.EPT.XmlIndexQuery.QueryDataTypes.DateTime)
'Define a criteria and a sort command
xfi.FilterCriteria = "[Area] > 1173"
xfi.SortCommand = "[CS_PublishDate] DESC"
'Limit the number of records
xfi.PageSize = 10
Response.Write("<table>" & Environment.NewLine)
Do
  Using xre As XmlFilterReader = xfi.FilterReader()
     'Get the data on the current page record by record
     While xre.Read()
       Response.Write("<tr>")
       Response.Write("<td>" & Server.HtmlEncode(xre("Head")) & "</td>")
       Response.Write("<td>" & Server.HtmlEncode(xre("Intro")) & "</td>")
       Response.Write("</tr>" & Environment.NewLine)
     End While
     'get data on the next page
     xfi.PageNumber += 1
  End Using
While xfi.PageNumber <= xfi.PageCount 
Response.Write("</table>" & Environment.NewLine)